package cn.oyjg.debugTool.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.tree.DefaultMutableTreeNode;

import org.apache.log4j.Logger;

import cn.oyjg.debugTool.entity.Action;
import cn.oyjg.debugTool.entity.Category;
import cn.oyjg.debugTool.entity.Param;

public class SQLiteHelper {
	private Logger log=Logger.getLogger(SQLiteHelper.class);
	static {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//保存树
	public Category saveTree(Category c){
		Connection conn=getConn();
		PreparedStatement pstmt=null ;
		ResultSet rs = null;
		try {
			if(c.getId()==null || c.getId()==0){
				pstmt = conn.prepareStatement("insert into category(name,pid) values(?,?)");
				pstmt.setString(1, c.getName());
				pstmt.setInt(2, c.getPid());
				pstmt.executeUpdate();			
				//获得主键
				rs=conn.prepareStatement("select max(id) id from category").executeQuery();
				c.setId(rs.getInt("id"));	
				
			}else{
				pstmt = conn.prepareStatement("update category set name=? where id=?");
				pstmt.setString(1, c.getName());
				pstmt.setInt(2, c.getId());
				pstmt.executeUpdate();			
			}
						
		} catch (Exception e) {
			log.error(e);
		}finally {
			close(conn,pstmt,null);
		}
		return c;		
	}
	//保存树
		public int deleteTree(int id){
			Connection conn=getConn();
			PreparedStatement pstmt=null ;
			int result=0;
			try {
				pstmt = conn.prepareStatement("delete from category where id=?");
				pstmt.setInt(1, id);
				pstmt.executeUpdate();
			} catch (Exception e) {
				log.error(e);			
			}finally {
				close(conn,pstmt,null);
			}
			return result;		
		}

	
	public Action saveAction(Action action,Category category,List<Param> param){
		Connection conn=getConn();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			conn.setAutoCommit(false);
			
			if(action.getId()==null || action.getId()==0){
				pstmt=conn.prepareStatement("insert into action(name,url,category,method,ser) values(?,?,?,?,?)");
				pstmt.setString(1, action.getName());
				pstmt.setString(2, action.getUrl());
				pstmt.setInt(3, category.getId());			
				pstmt.setString(4, action.getMethod());		
				pstmt.setBoolean(5, action.isSer());
				pstmt.executeUpdate();
				//查询ID
				//获得主键
				rs=conn.prepareStatement("select max(id) id from action").executeQuery();
				action.setId(rs.getInt("id"));	
				
			}else{
				pstmt=conn.prepareStatement("update action set url=?,method=?,ser=? where id=?");
				pstmt.setString(1, action.getUrl());
				pstmt.setString(2, action.getMethod());			
				pstmt.setBoolean(3, action.isSer());
				pstmt.setInt(4, action.getId());
				pstmt.executeUpdate();
			}
			
			
			pstmt=conn.prepareStatement("delete from param where actionid="+action.getId());
			pstmt.executeUpdate();
			
			param.forEach(p->{
				try {
					PreparedStatement pstmt2=conn.prepareStatement("insert into param(name,value,actionid) values(?,?,?) ");
					pstmt2.setString(1, p.getName());
					pstmt2.setObject(2, p.getValue());
					pstmt2.setInt(3, action.getId());
					pstmt2.executeUpdate();
				} catch (Exception e) {
					try {
						throw e;
					} catch (Exception e1) {
						log.error(e1);
					}
				}
			});
			
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.error(e1);
			}
			return null;
		}finally {
			close(conn, pstmt, rs);
		}
		
		return action;
	}
	public Connection getConn() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:data.db");
		} catch (SQLException e) {
			log.error(e);
			return null;
		}
		return connection;
	}
	
	public void treeInit(DefaultMutableTreeNode treeRoot){
		setTreeNode(treeRoot);
	}
	
	//设置树结构
	public void setTreeNode(DefaultMutableTreeNode treeRoot){
		Category rootC = (Category)(treeRoot.getUserObject());
		List<Category> catas=getChildByPid(rootC.getId());
		for(int i=0;i<catas.size();i++){
			DefaultMutableTreeNode node1 = new DefaultMutableTreeNode(catas.get(i));
			treeRoot.add(node1);
			if(isChild(catas.get(i).getId())>0){
				setTreeNode(node1);
			}
		}
        
	}
	
	private List<Category> getChildByPid(int id){
		List<Category> cata=null;
		Connection conn=getConn();
		Statement stmt=null ;
		ResultSet rs=null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from category where pid="+id);
			cata=new ArrayList<Category>();			
			while (rs.next()) {
				Category p=new Category(rs.getString("name"),rs.getInt("id"),rs.getInt("pid"));
				cata.add(p);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}finally {
			close(conn,stmt,rs);
		}
		return cata;
	}
	public int isChild(int id){
		Connection conn=getConn();
		Statement stmt =null ;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			stmt.setQueryTimeout(30); // set timeout to 30 sec.

			rs = stmt.executeQuery("select count(1) c from category where pid="+id);
			return rs.getInt("c");
		} catch (SQLException e) {
			return 0;
		}finally {
			close(conn, stmt, rs);
		}
		
	}
	public Action getActionByPid(int pid){
		Connection conn=getConn();
		Statement stmt =null ;
		ResultSet rs = null;
		try {
			stmt = getConn().createStatement();
			stmt.setQueryTimeout(30); // set timeout to 30 sec.

			rs = stmt.executeQuery("select * from action where category="+pid);
			Action action=new Action();
			action.setUrl(rs.getString("url"));
			action.setMethod(rs.getString("method"));
			action.setId(rs.getInt("id"));
			action.setPid(rs.getString("pid"));
			action.setCategory(rs.getString("category"));
			action.setName(rs.getString("name"));
			action.setSer(rs.getBoolean("ser"));
			return action;
		} catch (SQLException e) {
			return null;
		}finally {
			close(conn, stmt, rs);
		}
	}

	public List<Param> getParamByActionId(Integer aid) {
		List<Param> param=null;
		Connection conn=getConn();
		Statement stmt =null ;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			stmt.setQueryTimeout(30); // set timeout to 30 sec.

			rs = stmt.executeQuery("select * from param where actionid="+aid);
			param=new ArrayList<Param>();			
			while (rs.next()) {
				Param p=new Param();
				p.setName(rs.getString("name"));
				p.setValue(rs.getString("value"));
				param.add(p);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(conn, stmt, rs);
		}
		return param;

	}
	public int saveSetting(String value,Integer id){
		Connection conn=getConn();
		PreparedStatement pstmt=null ;
		int result=0;
		try {
			pstmt = conn.prepareStatement("update sysparam set value=? where id=?");
			pstmt.setString(1, value);
			pstmt.setInt(2, id);
			pstmt.executeUpdate();
		} catch (Exception e) {
			log.error(e);			
		}finally {
			close(conn,pstmt,null);
		}
		return result;		
	}
	
	public String querySetting(Integer id){
		Connection conn=getConn();
		Statement stmt=null ;
		ResultSet rs=null;
		String value="";
		try {
			stmt = conn.createStatement();
			rs=stmt.executeQuery("select * from sysparam where id="+id);
			if(rs.next()){
				value=rs.getString("value");
			}
			
		} catch (Exception e) {
			log.error(e);			
		}finally {
			close(conn,stmt,null);
		}
		return value;		
	}

	public static void main(String[] args) throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:data.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			statement.executeUpdate("drop table if exists person");
			statement.executeUpdate("create table person (id integer, name string)");
			statement.executeUpdate("insert into person values(1, 'leo')");
			statement.executeUpdate("insert into person values(2, 'yui')");
			ResultSet rs = statement.executeQuery("select * from person");
			while (rs.next()) {
				System.out.println("name = " + rs.getString("name"));
				System.out.println("id = " + rs.getInt("id"));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.err.println(e);
			}
		}
	}
	public void close(Connection conn,Statement stmt,ResultSet rs){
		try{
			if(rs!=null && !rs.isClosed()){
				rs.close();
			}
			if(stmt!=null && !stmt.isClosed()){
				stmt.close();
			}
			if(conn!=null && !conn.isClosed()){
				conn.close();
			}
		}catch(Exception err){
			err.printStackTrace();
		}
	}
}